import csv
from datetime import datetime, timedelta
import hashlib
import os
import sqlite3
from libs.utils import *
import time

base_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), '..')
log_dir_name = 'logs'
export_dir_name = 'exports'
detection_values_dir_name = 'detection_values'
log_dir = os.path.join(base_dir, log_dir_name)
export_dir = os.path.join(base_dir, export_dir_name)
detection_values_dir = os.path.join(base_dir, detection_values_dir_name)
param_configs = {
    'voltage': { 'name': '电压', 'unit': 'V', 'second_step': 1, 'value_size': 1, 'multiple': 1, 'value_offset': 0, 'invalid_value': 2**8 - 1, 'empty_value': 0, 'color': '#1f77b4', 'chart_show': True },
    'time_delay': { 'name': '时延', 'unit': 'ms', 'second_step': 10, 'value_size': 2, 'multiple': 1, 'value_offset': 0, 'invalid_value': 2**16 - 1, 'empty_value': 0, 'color': '#ff7f0e', 'chart_show': True },
    'network_speed_up': { 'name': '上行流量', 'unit': 'Kbps', 'second_step': 10, 'value_size': 2, 'multiple': 1, 'value_offset': 0, 'invalid_value': 2**16 - 1, 'empty_value': 2**16 - 2, 'color': '#2ca02c', 'chart_show': True },
    'network_speed_down': { 'name': '下行流量', 'unit': 'Kbps', 'second_step': 10, 'value_size': 2, 'multiple': 1, 'value_offset': 0, 'invalid_value': 2**16 - 1, 'empty_value': 2**16 - 2, 'color': '#d62728', 'chart_show': True },
    'cell_id': { 'name': '小区ID', 'unit': '', 'second_step': 30, 'value_size': 6, 'multiple': 1, 'value_offset': 0, 'invalid_value': 2**48 - 1, 'empty_value': 0, 'color': '#9467bd', 'chart_show': False },
    'cell_pci': { 'name': 'PCI', 'unit': '','second_step': 10, 'value_size': 2,'multiple': 1, 'value_offset': 0, 'invalid_value': 2**16 - 1, 'empty_value': 0, 'color': '#8c564b', 'chart_show': False },
    'cell_band': { 'name': '频段号', 'unit': '', 'second_step': 10, 'value_size': 1, 'multiple': 1, 'value_offset': 0, 'invalid_value': 2**8 - 1, 'empty_value': 0, 'color': '#9467bd', 'chart_show': True },
    'cell_rsrp': { 'name': 'RSRP', 'unit': 'dBm', 'second_step': 10, 'value_size': 1, 'multiple': -1, 'value_offset': 0, 'invalid_value': 2**8 - 1, 'empty_value': 0, 'color': '#8c564b', 'chart_show': True },
    'cell_sinr': { 'name': 'SINR', 'unit': 'dB', 'second_step': 10, 'value_size': 1, 'multiple': 1, 'value_offset': 128, 'invalid_value': 2**8 - 1, 'empty_value': 0, 'color': '#e377c2', 'chart_show': True },
    'cell_rssi': { 'name': 'RSSI', 'unit': 'dBm', 'second_step': 10, 'value_size': 1, 'multiple': -1, 'value_offset': 0, 'invalid_value': 2**8 - 1, 'empty_value': 0, 'color': '#bcbd22', 'chart_show': True },
    'cell_rsrq': { 'name': 'RSRQ', 'unit': 'dB', 'second_step': 10, 'value_size': 1, 'multiple': 1, 'value_offset': 128, 'invalid_value': 2**8 - 1, 'empty_value': 0, 'color': '#17becf', 'chart_show': True },
}

def rows_to_dict(cursor, rows):
    return [dict(zip([column[0] for column in cursor.description], row)) for row in rows]

def create_conn(database_path='data.db', max_retries=5, retry_delay=0.1):
    # 设置重试和超时机制，避免数据库被锁定
    for attempt in range(max_retries):
        try:
            db_path = os.path.join(base_dir, database_path)
            conn = sqlite3.connect(db_path, check_same_thread=False, timeout=10)  # 连接到名为data.db的数据库文件
            cursor = conn.cursor()
            return conn, cursor
        except sqlite3.OperationalError as e:
            if "database is locked" in str(e) and attempt < max_retries - 1:
                time.sleep(retry_delay * (attempt + 1))  # 指数退避
                continue
            print("Failed to connect to the database after multiple attempts")
            raise Exception("Failed to connect to the database after multiple attempts")
            

def fetchall(sql, param=()):
    conn, cursor = create_conn()
    cursor.execute(sql, param)
    rows = rows_to_dict(cursor, cursor.fetchall())
    cursor.close()
    conn.close()
    return rows

def fetchone(sql, param=()):
    conn, cursor = create_conn()
    cursor.execute(sql, param)
    row = cursor.fetchone()
    if row is not None:
        row = dict(zip([column[0] for column in cursor.description], row))
    cursor.close()
    conn.close()
    return row

def fetchvalue(sql, param=()):
    conn, cursor = create_conn()
    value = None
    cursor.execute(sql, param)
    row = cursor.fetchone()
    if row is not None:
        value = row[0]
    cursor.close()
    conn.close()
    return value

def execute(sql, param=()):
    conn, cursor = create_conn()
    last_id = None
    try:
        cursor.execute(sql, param)
        last_id = cursor.lastrowid
        conn.commit()
    except Exception as e:
        print("Error executing SQL: ", sql)
        print("Error message: ", e)
        conn.rollback()
    finally:
        cursor.close()
        conn.close()
    return last_id

def init_db(database_path='data.db'):
    conn, cursor = create_conn(database_path)
    # 创建用户表
    cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, password TEXT)''')
    # 创建设备表
    cursor.execute('''CREATE TABLE IF NOT EXISTS clients (id INTEGER PRIMARY KEY AUTOINCREMENT, client_id TEXT, client_name TEXT, client_ip TEXT, client_type TEXT, client_check_time TEXT, client_alive_time TEXT, client_online INTEGER, client_info TEXT, client_secret TEXT)''')
    # 创建局域网地址表
    cursor.execute('''CREATE TABLE IF NOT EXISTS lanips (lanip_id INTEGER PRIMARY KEY AUTOINCREMENT, client_id TEXT, lanip_name TEXT, lanip_ip TEXT, lanip_type TEXT, lanip_alive_time TEXT, lanip_enable INTEGER)''')
    # 创建命令表
    cursor.execute('''CREATE TABLE IF NOT EXISTS cmds (cmd_id INTEGER PRIMARY KEY AUTOINCREMENT, client_id TEXT, cmd TEXT, result TEXT, complete INTEGER, create_time TEXT, result_time TEXT)''')
    # 创建日志表
    cursor.execute('''CREATE TABLE IF NOT EXISTS logs (log_id INTEGER PRIMARY KEY AUTOINCREMENT, client_id TEXT, log_content TEXT, log_time TEXT)''')
    # 创建系统日志
    cursor.execute('''CREATE TABLE IF NOT EXISTS system_logs (log_id INTEGER PRIMARY KEY AUTOINCREMENT, log_content TEXT, log_time TEXT)''')
    # 创建任务表
    cursor.execute('''CREATE TABLE IF NOT EXISTS tasks (task_id INTEGER PRIMARY KEY AUTOINCREMENT, client_id TEXT, task_name TEXT, task_type TEXT, client_ip TEXT, client_port INTEGER, task_interval INTEGER, task_enable INTEGER, task_result INTEGER, task_last_time TEXT)''')
    # 创建告警计划表
    cursor.execute('''CREATE TABLE IF NOT EXISTS alarm_plans (plan_id INTEGER PRIMARY KEY AUTOINCREMENT, client_id TEXT, plan_name TEXT, plan_enable INTEGER, plan_maxtime INTEGER, plan_rangetime INTEGER, plan_maxcount INTEGER, plan_count TEXT, plan_begintime TEXT, plan_endtime TEXT, plan_active INTEGER, plan_template TEXT, plan_noticetype TEXT)''')
    # 创建告警记录表
    cursor.execute('''CREATE TABLE IF NOT EXISTS alarm_records (alarm_id INTEGER PRIMARY KEY AUTOINCREMENT, client_id TEXT, plan_id INTEGER, alarm_time TEXT, alarm_content TEXT, alarm_noticetype TEXT, alarm_read INTEGER, alarm_noticed INTEGER)''')
    # 创建告警通知表
    cursor.execute('''CREATE TABLE IF NOT EXISTS alarm_notices (notice_id INTEGER PRIMARY KEY AUTOINCREMENT, notice_source TEXT, app_id TEXT, app_name TEXT, notice_time TEXT, notice_content TEXT, notice_type TEXT, notice_sent INTEGER, notice_target TEXT)''')
    # 创建app表
    cursor.execute('''CREATE TABLE IF NOT EXISTS apps (id INTEGER PRIMARY KEY AUTOINCREMENT, app_id TEXT, app_name TEXT, app_secret TEXT, app_enable INTEGER, app_checked INTEGER)''')
    # 创建配置表
    cursor.execute('''CREATE TABLE IF NOT EXISTS configs (config_id INTEGER PRIMARY KEY AUTOINCREMENT, config_name TEXT, config_value TEXT, config_desc TEXT)''')
    # 设备相关参数，可以是电压、电流、功率、网络时延等。这是当前值，不是历史值，历史值需要从TSData中获取
    cursor.execute('''CREATE TABLE IF NOT EXISTS detection_values (client_id TEXT, type TEXT, value INTEGER, time TEXT)''')
    # 设备信息，包括品牌型号、序列号、位置、安装位置、安装日期、设备类型、账户、密码、备注等
    cursor.execute('''CREATE TABLE IF NOT EXISTS client_infos (client_id TEXT, model TEXT, serial TEXT, location TEXT, install_location TEXT, install_date TEXT, type TEXT, ip TEXT, port INTEGER, username TEXT, password TEXT, api_enable INTEGER, remark TEXT )''')
    
    conn.commit()
    # 验证是否有用户，如果没有就创建默认用户
    cursor.execute("SELECT COUNT(*) FROM users")
    if cursor.fetchone()[0] == 0:
        cursor.execute("INSERT INTO users (username, password) VALUES (?, ?)", ('admin', hashlib.sha256('admin'.encode()).hexdigest()))
    # 验证配置是否存在，如果不存在就创建默认配置
    cursor.execute("SELECT COUNT(*) FROM configs")
    if cursor.fetchone()[0] == 0:
        # 告警通知相关
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('alarm_notice_interval', '60', '告警通知间隔。单位：秒'))
        # 邮箱
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('email_enable', '0', '是否启用邮箱通知'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('email_smtp_server', 'smtp.qq.com', ''))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('email_smtp_port', '587', ''))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('email_sender_address', '', '发送者邮箱'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('email_sender_password', '', '邮箱密码或授权码'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('email_subject', '网络故障提醒', ''))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('email_receiver_address', '', '目标邮箱'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('email_common_target', '', '共用邮箱，如果从app来的告警通知中没有邮箱，则使用此邮箱发送邮件'))
        # 短信
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('sms_enable', '0', '是否启用短信通知'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('sms_type', 'tencent', '短信服务商，目前支持腾讯云'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('sms_tencent_sign_name', '', '短信签名'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('sms_tencent_secret_key', '', ''))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('sms_tencent_secret_id', '', ''))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('sms_tencent_template_id', '', ''))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('sms_phone', '', '短信发送的目标手机号，可以保证不同app发送给不同的接收者'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('sms_common_target', '', '共用手机号，如果从app来的告警通知中没有手机号，则使用此手机号发送短信'))
        # 连接中继服务器相关
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('notice_force_relay', '0', '是否强制使用中继服务发送通知，是本机将消息发送给中继服务器，再由中继服务器发送给目标'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('relay_url', 'http://127.0.0.1:5000', '中继服务器URL'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('relay_password', '123456', '中继服务器密码，用于注册时验证'))
        # relay_server_password
        # 中继服务相关
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('relay_service_enable', '0', '是否启用中继服务，是给别人提供中继服务的意思'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('relay_service_password', '123456', '中继服务密码，用于验证APP'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('relay_register_enable', '0', '是否允许别人注册中继服务'))
        # 本机相关
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('app_id', generate_random_string(), '发送告警通知时，告诉中继服务器自己的ID'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('app_name', '测试项目', '发送告警通知时，告诉中继服务器自己的名字'))
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('app_secret', generate_random_string(), '产生密钥发送给中继服务器，避免被恶意模仿'))
        # 日志相关
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('log_maxcount', '50000', '日志文件最大条数，超过将会将日志进行自动归档'))
        # 命令相关
        # 默认服务器IP
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('cmd_server_ip', '127.0.0.1', '默认服务器地址'))
        # 默认服务器端口
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?, ?, ?)", ('cmd_server_port', '8888', '默认服务器端口'))
        # iperf3服务端口
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?,?,?)", ('cmd_iperf3_server_port', '5201', 'iperf3服务端口'))
        # 是否开启iperf3服务
        cursor.execute("INSERT INTO configs (config_name, config_value, config_desc) VALUES (?,?,?)", ('cmd_iperf3_server_enable', '0', '是否开启iperf3服务'))
        
    conn.commit()
    cursor.close()
    conn.close()


config_map = {}
def get_config(config_name):
    # global config_map
    # if config_name in config_map:
    #     return config_map[config_name]
    # else:
    value = fetchvalue("SELECT config_value FROM configs WHERE config_name = ?", (config_name,))
    if value is None:
        return None
    config_map[config_name] = value
    return value

def set_config(config_name, config_value):
    # global config_map
    # config_map[config_name] = config_value
    return execute("UPDATE configs SET config_value = ? WHERE config_name = ?", (config_value, config_name))

def remove_db_file(database_path='data.db'):
    db_path = os.path.join(base_dir, database_path)
    if os.path.exists(db_path):
        os.remove(db_path)

def get_archive_log_dbs():
    archive_dbs = []
    for file in os.listdir(log_dir):
        if file.endswith('.db') and file != 'data.db':
            # 仅返回文件名称，不包括路径
            archive_dbs.append(file)
    return archive_dbs

def export_data(cursor, rows):
    # 导出的文件保存在exports文件夹中，根据导出时间生成文件名
    export_time = datetime.now().strftime('%Y-%m-%d_%H-%M-%S')
    file_name = f'{export_dir_name}/data_{export_time}.csv'
    file_path = os.path.join(base_dir, file_name)
    # 将查询结果写入 CSV 文件
    with open(file_path, mode='w', newline='') as file:
        writer = csv.writer(file)

        # 写入 CSV 文件的列名
        writer.writerow([description[0] for description in cursor.description])

        # 写入数据
        writer.writerows(rows)

    return file_name

def archive_logs(limit_time: datetime, from_db_path = 'data.db'):
    # 将日志从 from_db_path 导出并归档到 to_db_path
    real_limit_time = limit_time + timedelta(days=1)
    # 根据时间生成归档日志名
    archive_path = log_dir_name + '/log_' + limit_time.strftime('%Y-%m-%d') + '.db'
    # 归档日志
    init_db(archive_path)
    current_conn, current_cursor = create_conn()
    archive_conn, archive_cursor = create_conn(archive_path)
    try:
        data_to_archive = current_cursor.execute('SELECT * FROM logs WHERE log_time < ?', (real_limit_time.strftime('%Y-%m-%d'),))
        archive_cursor.executemany('INSERT INTO logs VALUES (?, ?, ?, ?)', data_to_archive)
        archive_conn.commit()
        archive_cursor.execute('SELECT COUNT(*) FROM logs')
        archived_logs_count = archive_cursor.fetchone()[0]
        current_cursor.execute('DELETE FROM logs WHERE log_time < ?', (real_limit_time.strftime('%Y-%m-%d'),))
        current_conn.commit()
        archive_cursor.close()
        archive_conn.close()
        current_cursor.close()
        current_conn.close()
    except Exception as e:
        print(f"An error occurred: {e}")
        current_cursor.close()
        current_conn.close()
        archive_cursor.close()
        archive_conn.close()
        raise e
    return archived_logs_count, archive_path

def system_log(log_content):
    # 获取IP地址
    ip = get_ip()
    # 构造日志内容
    log_content = f"{ip} - {log_content}"
    # 将日志内容写入文件
    execute("INSERT INTO system_logs (log_content, log_time) VALUES (?, ?)", (log_content, get_time()))

def device_log(client_id, log_content):
    # 将日志内容写入文件
    execute("INSERT INTO logs (client_id, log_content, log_time) VALUES (?, ?, ?)", (client_id, log_content, get_time()))

def update_detection_value(cursor, client_id, value_type, value, check_time):
    # 记录最新测试结果，删除并插入最新测试结果
    cursor.execute("DELETE FROM detection_values WHERE client_id =? and type =?", (client_id, value_type))
    if value is not None:
        cursor.execute("INSERT INTO detection_values (client_id, type, time, value) VALUES (?,?,?,?)", (client_id, value_type, check_time, value))



# 写一个函数，实现写log到文件中，并且自动换行，并且自动归档，并判断文件是否被占用，如果被占用则等待文件释放后再写入
def write_log_to_file(log_file, log_content):
    # 如果文件正在被写入，等待写入完成
    # lock_file = log_file + '.lock'
    # while os.path.exists(lock_file):
    #     # 判断文件创建时间，如果是5秒前创建的，则删除文件
    #     if (datetime.now() - datetime.fromtimestamp(os.path.getctime(lock_file))).seconds > 5:
    #         os.remove(lock_file)
    #     else:
    #         # 否则等待0.01秒后再次检查
    #         time.sleep(0.01)
    # 创建一个空文件，用于标记文件正在被写入
    try:
        # open(lock_file, 'w').close()
        # 打开文件，以追加模式写入
        with open(log_file, 'a', encoding='utf-8') as file:
            # 写入日志内容
            file.write(log_content + '\n')
        # 检查文件大小是否超过10MB，如果超过则重命名文件并创建新文件
        if os.path.getsize(log_file) > 1024 * 1024 * 10:
            # 获取当前时间
            current_time = datetime.now().strftime('%Y%m%d%H%M%S')
            # 重命名文件
            os.rename(log_file, f'{log_file}.{current_time}')
            # 创建新文件
            open(log_file, 'w').close()
    except Exception as e:
        print(f"Error: {e}")
    
    # 删除锁文件
    # if os.path.exists(lock_file):
    #     try:
    #         os.remove(lock_file)
    #     except Exception as e:
    #         print(f"Error: {e}")
    return True

# 记录日志到文件，记录时间等信息
def log_to_file(log_file, log_content, log_level='INFO', print_log=True):
    # 获取当前时间
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # 构造日志内容
    log_message = f'{current_time} [{log_level}] {log_content}'
    # 写入日志文件
    write_log_to_file(log_file, log_message)
    if print_log:
        print(log_message)
    return True

# 记录信息日志到文件
def log_info(log_content, print_log=True):
    return log_to_file(os.path.join(log_dir, 'info.log'), log_content, log_level='INFO', print_log=print_log)

# 记录错误日志到文件
def log_error(log_content, print_log=True):
    return log_to_file(os.path.join(log_dir, 'error.log'), log_content, log_level='ERROR', print_log=print_log)
